﻿using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace AD.EngenhariaTransito
{
    public class Endereco
    {

        public static DataSet PesquisarEndereco(string caixaPostal)
        {
            Database myDatabase = DatabaseFactory.CreateDatabase();
            DbCommand myCommand = myDatabase.GetStoredProcCommand("dbo.proc_EnderecoPesquisar");

            myDatabase.AddInParameter(myCommand, "@CaixaPostal", DbType.String, caixaPostal);

            DataSet ds = myDatabase.ExecuteDataSet(myCommand);
            return ds;
        }

        public static DataSet PesquisarEstado()
        {
            Database myDatabase = DatabaseFactory.CreateDatabase();
            DbCommand myCommand = myDatabase.GetSqlStringCommand(" Select IdEstado, Descricao From dbo.EnderecoEstado ORDER BY Descricao ");

            DataSet ds = myDatabase.ExecuteDataSet(myCommand);
            return ds;
        }

        public static DataSet PesquisarCidade(int estado)
        {
            Database myDatabase = DatabaseFactory.CreateDatabase();
            DbCommand myCommand = myDatabase.GetSqlStringCommand("select IdCidade, Descricao From dbo.EnderecoCidade where IdEstado = " + estado + " order by Descricao ");

            DataSet ds = myDatabase.ExecuteDataSet(myCommand);
            return ds;
        }

        public static DataSet PesquisarBairro(int cidade)
        {
            Database myDatabase = DatabaseFactory.CreateDatabase();
            DbCommand myCommand = myDatabase.GetSqlStringCommand(" select IdBairro, Descricao from dbo.EnderecoBairro Where IdCidade = " + cidade + " order by Descricao ");

            DataSet ds = myDatabase.ExecuteDataSet(myCommand);
            return ds;
        }

        public static DataSet IncluirEndereco(string Bairro, string CEP, string Endereco)
        {
            Database myDatabase = DatabaseFactory.CreateDatabase();
            DbCommand myCommand = myDatabase.GetStoredProcCommand("dbo.proc_Pedala_CadastroEndereco");

            myDatabase.AddInParameter(myCommand, "@Bairro", DbType.String, Bairro);
            myDatabase.AddInParameter(myCommand, "@CEP", DbType.String, CEP);
            myDatabase.AddInParameter(myCommand, "@Endereco", DbType.String, Endereco);

            DataSet ds = myDatabase.ExecuteDataSet(myCommand);
            return ds;
        }

        public static DataSet IncluirEnderecoCEP(int bairro, string Endereco, string CEP)
        {
            Database myDatabase = DatabaseFactory.CreateDatabase();
            DbCommand myCommand = myDatabase.GetStoredProcCommand("dbo.proc_Pedala_CadastroEnderecoCEP");

            myDatabase.AddInParameter(myCommand, "@CodBairro", DbType.Int32, bairro);
            myDatabase.AddInParameter(myCommand, "@Endereco", DbType.String, Endereco);
            myDatabase.AddInParameter(myCommand, "@CEP", DbType.String, CEP);

            DataSet ds = myDatabase.ExecuteDataSet(myCommand);
            return ds;
        }


    }
}
